package com.orange;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import org.cncitrus.data.common.Util;

import orange.database.DBIns;

public class Oztradeconn extends BaseAction {
	public Oztradeconn(String flag, String[] str1, String[] str2) throws SQLException {
		super();
		String sql;
		ResultSet rs;
		Connection conn = DBIns.getInstance().getConnection();
		Statement stmt = conn.createStatement();
		switch (Integer.parseInt(flag)) {
		case 1:
			sql = change1_2_1(str1, str2);
			System.out.println(sql);
			rs = stmt.executeQuery(sql);
			value = combine1_2_1(rs);
			break;
		case 2:
			sql = change1_2_2(str1, str2);
			System.out.println(sql);
			rs =stmt.executeQuery(sql);
			value = combine1_2_2(rs);
			break;
		case 3:
			sql = change1_2_3(str1, str2);
			System.out.println(sql);
			rs = stmt.executeQuery(sql);
			value = combine1_2_3(rs);
			break;
		}
		DBIns.getInstance().release(conn);
	}

	public String change1_2_1(String[] str1, String[] str2) {
		try {
			nianfen = Util.Convert(str1);
			bianhao = Util.Convert(str2);

			int n = str1.length;

			int m = str2.length;

			String nnianfen = new String(str1[n - 1].getBytes("ISO-8859-1"),
					"utf-8");
			String bbianhao = new String(str2[m - 1].getBytes("ISO-8859-1"),
					"utf-8");
//			String nnianfen = new String(str1[n - 1].getBytes(),"utf-8");
//			String bbianhao = new String(str2[m - 1].getBytes(),"utf-8");

			String sql = "1";

			if (nnianfen.equals("所有年份")) {
				if (bbianhao.equals("所有品种"))
					sql = "select orange_z_jinchuliang.BianHao,form1_2_bianhao.show,form1_2_bianhao.Y_MingCheng,orange_z_jinchuliang.NianFen,orange_z_jinchuliang.Jin_ShuLiang,orange_z_jinchuliang.Jin_JinE,orange_z_jinchuliang.Chu_ShuLiang,orange_z_jinchuliang.Chu_JinE from orange_z_jinchuliang,form1_2_bianhao where orange_z_jinchuliang.BianHao=form1_2_bianhao.value order by NianFen";
				else
					sql = "select orange_z_jinchuliang.BianHao,form1_2_bianhao.show,form1_2_bianhao.Y_MingCheng,orange_z_jinchuliang.NianFen,orange_z_jinchuliang.Jin_ShuLiang,orange_z_jinchuliang.Jin_JinE,orange_z_jinchuliang.Chu_ShuLiang,orange_z_jinchuliang.Chu_JinE from orange_z_jinchuliang,form1_2_bianhao where orange_z_jinchuliang.BianHao=form1_2_bianhao.value and orange_z_jinchuliang.BianHao in("
							+ bianhao + ") order by NianFen";
			} else {
				if (bbianhao.equals("所有品种"))
					sql = "select orange_z_jinchuliang.BianHao,form1_2_bianhao.show,form1_2_bianhao.Y_MingCheng,orange_z_jinchuliang.NianFen,orange_z_jinchuliang.Jin_ShuLiang,orange_z_jinchuliang.Jin_JinE,orange_z_jinchuliang.Chu_ShuLiang,orange_z_jinchuliang.Chu_JinE from orange_z_jinchuliang,form1_2_bianhao where orange_z_jinchuliang.BianHao=form1_2_bianhao.value and orange_z_jinchuliang.NianFen in("
							+ nianfen + ") order by NianFen";
				else
					sql = "select orange_z_jinchuliang.BianHao,form1_2_bianhao.show,form1_2_bianhao.Y_MingCheng,orange_z_jinchuliang.NianFen,orange_z_jinchuliang.Jin_ShuLiang,orange_z_jinchuliang.Jin_JinE,orange_z_jinchuliang.Chu_ShuLiang,orange_z_jinchuliang.Chu_JinE from orange_z_jinchuliang,form1_2_bianhao where orange_z_jinchuliang.BianHao=form1_2_bianhao.value and orange_z_jinchuliang.BianHao in("
							+ bianhao
							+ ") and orange_z_jinchuliang.NianFen in("
							+ nianfen + ") order by NianFen";
			}
			return sql;
		} catch (Exception e) {
			return "error";
		}
	}

	public String combine1_2_1(ResultSet rs1) {
//		Beforefirst(rs1);
		StringBuffer buffer1 = new StringBuffer();
		buffer1
				.append("<thead><tr><td>年份</td><td>编码</td><td>中文名称</td><td>英文名称</div></td><td>进口总量(Kg)</td><td >进口金额(千美元)</td><td >出口总量(Kg)</td><td >出口金额(千美元)</td></tr></thead>");
		try {
			while (rs1.next()) {
				buffer1.append("<tr><td>" + rs1.getObject("NianFen")
						+ "</td><td>" + rs1.getObject("BianHao") + "</td><td>"
						+ rs1.getObject("show") + "</td><td>"
						+ rs1.getObject("Y_MingCheng") + "</td><td>"
						+ rs1.getObject("Jin_ShuLiang") + "</td><td>"
						+ rs1.getObject("Jin_JinE") + "</td><td>"
						+ rs1.getObject("Chu_ShuLiang") + "</td><td>"
						+ rs1.getObject("Chu_JinE") + "</td></tr>");
			}
			value = new String(buffer1);
			return value;
		} catch (Exception e) {
			return "-1";
		}
	}

	public String change1_2_2(String[] str1, String[] str2) {
		try {

			int n = str1.length;

			nianfen = Util.Convert(str1);

			int m = str2.length;
			bianhao = Util.Convert(str2);

			String nnianfen = new String(str1[n - 1].getBytes("ISO-8859-1"),
					"utf-8");
			String bbianhao = new String(str2[m - 1].getBytes("ISO-8859-1"),
					"utf-8");
//			String nnianfen = new String(str1[n - 1].getBytes());
//			String bbianhao = new String(str2[m - 1].getBytes());
			String sql = "1";

			if (nnianfen.equals("所有年份")) {
				if (bbianhao.equals("所有品种"))
					sql = "select orange_z_chuliang.BianHao,form1_2_bianhao.show,form1_2_bianhao.Y_MingCheng,orange_z_chuliang.NianFen,orange_z_chuliang.Chu_ShuLiang,orange_z_chuliang.Chu_JinE,orange_z_chuliang.GuoJia from orange_z_chuliang,form1_2_bianhao where orange_z_chuliang.BianHao=form1_2_bianhao.value order by NianFen";
				else
					sql = "select orange_z_chuliang.BianHao,form1_2_bianhao.show,form1_2_bianhao.Y_MingCheng,orange_z_chuliang.NianFen,orange_z_chuliang.Chu_ShuLiang,orange_z_chuliang.Chu_JinE,orange_z_chuliang.GuoJia from orange_z_chuliang,form1_2_bianhao where orange_z_chuliang.BianHao=form1_2_bianhao.value and orange_z_chuliang.BianHao in("
							+ bianhao + ") order by NianFen";
			} else {
				if (bbianhao.equals("所有品种"))
					sql = "select orange_z_chuliang.BianHao,form1_2_bianhao.show,form1_2_bianhao.Y_MingCheng,orange_z_chuliang.NianFen,orange_z_chuliang.Chu_ShuLiang,orange_z_chuliang.Chu_JinE,orange_z_chuliang.GuoJia from orange_z_chuliang,form1_2_bianhao where orange_z_chuliang.BianHao=form1_2_bianhao.value and orange_z_chuliang.NianFen in("
							+ nianfen + ") order by NianFen";
				else
					sql = "select orange_z_chuliang.BianHao,form1_2_bianhao.show,form1_2_bianhao.Y_MingCheng,orange_z_chuliang.NianFen,orange_z_chuliang.Chu_ShuLiang,orange_z_chuliang.Chu_JinE,orange_z_chuliang.GuoJia from orange_z_chuliang,form1_2_bianhao where orange_z_chuliang.BianHao=form1_2_bianhao.value and orange_z_chuliang.BianHao in("
							+ bianhao
							+ ") and orange_z_chuliang.NianFen in("
							+ nianfen + ") order by NianFen";
			}
			return sql.toLowerCase();
		} catch (Exception e) {
			return "error";
		}
	}

	public String combine1_2_2(ResultSet rs1) {
//		Beforefirst(rs1);
		StringBuffer buffer1 = new StringBuffer();
		buffer1
				.append("<thead><tr><td>年份</td><td>编码</td><td>中文名称</td><td>英文名称</td><td>进口国家</td><td>中国出口总量(Kg)</td><td >中国出口金额(千美元)</td></tr></thead>");
		try {
			while (rs1.next()) {
				buffer1.append("<tr><td>" + rs1.getObject("NianFen")
						+ "</td><td>" + rs1.getObject("BianHao") + "</td><td>"
						+ rs1.getObject("show") + "</td><td>"
						+ rs1.getObject("Y_MingCheng") + "</td><td>"
						+ rs1.getObject("GuoJia") + "</td><td>"
						+ rs1.getObject("Chu_ShuLiang") + "</td><td>"
						+ rs1.getObject("Chu_JinE") + "</td></tr>");
			}
			value = new String(buffer1);
			return value;
		} catch (Exception e) {
			return "-1";
		}
	}

	public String change1_2_3(String[] str1, String[] str2) {
		try {

			int n = str1.length;
			nianfen = Util.Convert(str1);

			int m = str2.length;
			bianhao = Util.Convert(str2);

			String nnianfen = new String(str1[n - 1].getBytes("ISO-8859-1"),
					"utf-8");
			String bbianhao = new String(str2[m - 1].getBytes("ISO-8859-1"),
					"utf-8");
//			String nnianfen = new String(str1[n - 1].getBytes());
//			String bbianhao = new String(str2[m - 1].getBytes());

			String sql = "1";

			if (nnianfen.equals("所有年份")) {
				if (bbianhao.equals("所有品种"))
					sql = "select orange_z_jinliang.BianHao,form1_2_bianhao.show,form1_2_bianhao.Y_MingCheng,orange_z_jinliang.NianFen,orange_z_jinliang.Jin_ShuLiang,orange_z_jinliang.Jin_JinE,orange_z_jinliang.GuoJia from orange_z_jinliang,form1_2_bianhao where orange_z_jinliang.BianHao=form1_2_bianhao.value order by NianFen";
				else
					sql = "select orange_z_jinliang.BianHao,form1_2_bianhao.show,form1_2_bianhao.Y_MingCheng,orange_z_jinliang.NianFen,orange_z_jinliang.Jin_ShuLiang,orange_z_jinliang.Jin_JinE,orange_z_jinliang.GuoJia from orange_z_jinliang,form1_2_bianhao where orange_z_jinliang.BianHao=form1_2_bianhao.value and orange_z_jinliang.BianHao in ("
							+ bianhao + ") order by NianFen";
			} else {
				if (bbianhao.equals("所有品种"))
					sql = "select orange_z_jinliang.BianHao,form1_2_bianhao.show,form1_2_bianhao.Y_MingCheng,orange_z_jinliang.NianFen,orange_z_jinliang.Jin_ShuLiang,orange_z_jinliang.Jin_JinE,orange_z_jinliang.GuoJia from orange_z_jinliang,form1_2_bianhao where orange_z_jinliang.BianHao=form1_2_bianhao.value and orange_z_jinliang.NianFen in("
							+ nianfen + ") order by NianFen";
				else
					sql = "select orange_z_jinliang.BianHao,form1_2_bianhao.show,form1_2_bianhao.Y_MingCheng,orange_z_jinliang.NianFen,orange_z_jinliang.Jin_ShuLiang,orange_z_jinliang.Jin_JinE,orange_z_jinliang.GuoJia from orange_z_jinliang,form1_2_bianhao where orange_z_jinliang.BianHao=form1_2_bianhao.value and orange_z_jinliang.BianHao in("
							+ bianhao
							+ ") and orange_z_jinliang.NianFen in("
							+ nianfen + ") order by NianFen";
			}
			return sql.toLowerCase();
		} catch (Exception e) {
			return "error";
		}
	}

	public String combine1_2_3(ResultSet rs1) {
//		Beforefirst(rs1);
		StringBuffer buffer1 = new StringBuffer();
		buffer1
				.append("<thead><tr><td>年份</td><td>编码</td><td>中文名称</td><td>英文名称</td><td>出口国家</td><td>中国进口总量(Kg)</td><td >中国进口金额(千美元)</td></tr></thead>");
		try {
			while (rs1.next()) {
				buffer1.append("<tr><td>" + rs1.getObject("NianFen")
						+ "</td><td>" + rs1.getObject("BianHao") + "</td><td>"
						+ rs1.getObject("show") + "</td><td>"
						+ rs1.getObject("Y_MingCheng") + "</td><td>"
						+ rs1.getObject("GuoJia") + "</td><td>"
						+ rs1.getObject("Jin_ShuLiang") + "</td><td>"
						+ rs1.getObject("Jin_JinE") + "</td></tr>");
			}
			value = new String(buffer1);
			return value;
		} catch (Exception e) {
			return "-1";
		}
	}
	public static void main(String[] arg){
		try {
			String[] str1 = { "2005", "2006" }, str2 = { "所有品种" };
			Oztradeconn oz_1=new Oztradeconn("1",str1, str2);
			Oztradeconn oz_2=new Oztradeconn("2",str1, str2);
			Oztradeconn oz_3=new Oztradeconn("3",str1, str2);
			System.out.println(oz_1.getValue());
			System.out.println(oz_2.getValue());
			System.out.println(oz_3.getValue());
		} catch (Exception e) {
			// TODO: handle exception
		}
	}

}
